﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using SundStudent.ModelLayer;

namespace SundStudent.DBLayer
{
     public class DBLektionsFamilie
    {
        private SqlCommand dbCmd = null;

        public Familie FindFamilie( int pnr)
        {
            string sql = @"select nr, navn, adresse, postnr, byen, telefon, relation from Lektionsfamilie " +
                           " where patient  = " + pnr;
                       

            Console.WriteLine("sql stmt = " + sql);

            dbCmd = DBConnection.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = dbCmd.ExecuteReader();

            Familie famObj = new Familie();

            if (dbReader.Read())
            {
                try
                {
                    Console.WriteLine("antal felter " + dbReader.FieldCount);
                    famObj.Navn = dbReader["navn"].ToString();
                    famObj.Nr = Convert.ToInt32(dbReader["nr"].ToString());
                    famObj.Adresse = dbReader["adresse"].ToString();
                    famObj.Relation = dbReader["relation"].ToString();

                }
                catch (Exception e)
                {
                    Console.WriteLine("LektionsFamilie objekt ikke oprettet korrekt");
                    famObj = null;
                }


            }

            else
            {
                famObj = null;
            }
            dbReader.Close();
            DBConnection.Close();
            return famObj;
        }


        
        public int InsertFamilie(Familie famObj)
        {
            int rc = -1;
            SqlCommand dbCmd = null;
            try
            { // tildel naeste nr
                String sql = "select max(nr) as nextnr from Lektionsfamilie";
                SqlCommand dbCmd1 = DBConnection.GetDbCommand(sql);
                IDataReader dbReader;
                dbReader = dbCmd1.ExecuteReader();
                if (dbReader.Read())
                {
                    try
                    {
                        famObj.Nr = Convert.ToInt32(dbReader["nextnr"].ToString()) + 1;
                        Console.WriteLine("næstenr familieObj: " + famObj.Nr);

                    }
                    catch (Exception exobj)
                    {
                        famObj.Nr = 1;
                    }
                }
                DBConnection.Close();
                Console.WriteLine("næstenr familieObj: " + famObj.Nr);

                dbCmd = DBConnection.GetDbCommand();

                dbCmd.CommandText = " insert into Lektionsfamilie (nr, navn,adresse, postnr, byen, telefon,relation,patient)" +
                       "values (@fnr,@fnavn,@fadresse,@fpostnr,@fbyen,@ftlf,@frelation,@pnr)";

                SqlParameter fnrParam = new SqlParameter("@fnr", SqlDbType.Int);
                SqlParameter fnavnParam = new SqlParameter("@fnavn", SqlDbType.VarChar, 30);
                SqlParameter fadresseParam = new SqlParameter("@fadresse", SqlDbType.VarChar, 30);
                SqlParameter fpostnrParam = new SqlParameter("@fpostnr", SqlDbType.Int);
                SqlParameter fbyenParam = new SqlParameter("@fbyen", SqlDbType.VarChar, 30);
                SqlParameter ftelefonParam = new SqlParameter("@ftlf", SqlDbType.Int);
                SqlParameter frelationParam = new SqlParameter("@frelation", SqlDbType.VarChar,30);
                SqlParameter fpnrParam = new SqlParameter("@pnr", SqlDbType.Int);
                //set the prameters value
                fnrParam.Value = famObj.Nr;
                fnavnParam.Value = famObj.Navn;
                fadresseParam.Value = famObj.Patient.Adresse;
                fpostnrParam.Value = famObj.Patient.Postnr;
                ftelefonParam.Value = famObj.Telefon;
                frelationParam.Value = famObj.Relation;
                fbyenParam.Value = famObj.Patient.Byen;
                fpnrParam.Value = famObj.Patient.Nr;


                //set the prameters on the command object
                dbCmd.Parameters.Add(fnrParam);
                dbCmd.Parameters.Add(fnavnParam);
                dbCmd.Parameters.Add(fadresseParam);
                dbCmd.Parameters.Add(fpostnrParam);
                dbCmd.Parameters.Add(fbyenParam);
                dbCmd.Parameters.Add(ftelefonParam);
                dbCmd.Parameters.Add(frelationParam);
                dbCmd.Parameters.Add(fpnrParam);

                rc = dbCmd.ExecuteNonQuery();


            }
            catch (Exception insertE)
            {
                Console.WriteLine("Fejl i insert lektionsfamilie");
                Console.WriteLine(insertE);
            }
            DBConnection.Close();
            return rc;
        }
    }
}
